use crate::app::APP_CONFIG;
use crate::structs::bs_user::BSUser;
use serde::{Deserialize, Serialize};
use std::cmp::max;

use crate::structs::message::Message;
use rusqlite::{Connection, Error, Result as SQLResult};

#[derive(Deserialize, Serialize, Debug, PartialEq, Builder)]
#[builder(name = "MessageConstructor")]
pub struct DBMessage {
    pub from_user: String,
    pub to_cap: String,
    pub text: String,
    pub freq: u32,
    id: u32,
}

pub fn init_db() -> Result<Connection, Error> {
    let db = Connection::open(APP_CONFIG.write().unwrap().get_bs_db())?;
    init_tables(&db)?;
    Ok(db)
}

fn init_tables(db: &Connection) -> SQLResult<()> {
    db.execute(
        "CREATE TABLE IF NOT EXISTS message (
            id    integer NOT NULL PRIMARY KEY AUTOINCREMENT,
            from_user  TEXT NOT NULL,
            to_cap  TEXT NOT NULL,
            text    TEXT NOT NULL,
            freq    integer NOT NULL
        )",
        (), // empty list of parameters.
    )?;
    db.execute(
        "CREATE TABLE IF NOT EXISTS pager_bs (
            id integer NOT NULL PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            password TEXT NOT NULL,
            offset INTEGER NOT NULL
        );",
        (), // empty list of parameters.
    )?;
    db.execute(
        "CREATE UNIQUE INDEX IF NOT EXISTS paget_bs_ix_1 ON pager_bs(name DESC);",
        (),
    )?;

    Ok(())
}

pub fn create_user_in_db(u: BSUser) -> SQLResult<BSUser> {
    let db = Connection::open(APP_CONFIG.write().unwrap().get_bs_db())?;
    let d = format!(
        "INSERT INTO pager_bs \
        (name, password, offset) \
        VALUES ('{}', '{}', {})",
        u.username, u.password, 0
    );
    db.execute(
        &d,
        (), // empty list of parameters.
    )?;
    Ok(get_user_in_db(&u.username)?.unwrap())
}

pub fn get_user_in_db(name: &str) -> SQLResult<Option<BSUser>> {
    let db = Connection::open(APP_CONFIG.write().unwrap().get_bs_db())?;
    let mut stmt = db.prepare("SELECT id, name, password, offset FROM pager_bs where name = ?1")?;
    let user_bs_iter = stmt
        .query_map([name], |row| {
            Ok(BSUser {
                username: row.get(1)?,
                password: row.get(2)?,
                offset: row.get(3)?,
            })
        })?
        .next();
    if user_bs_iter.is_some() {
        Ok(Some(user_bs_iter.unwrap()?))
    } else {
        Ok(None)
    }
}

pub fn insert_user_message(m: Message) -> SQLResult<()> {
    let db = Connection::open(APP_CONFIG.write().unwrap().get_bs_db())?;
    let mut stmt = db.prepare(
        "INSERT INTO message (from_user, to_cap, text, freq) \
    VALUES (?1, ?2, ?3, ?4)",
    )?;
    stmt.execute([m.from, m.to_cap, m.text, m.freq.to_string()])?;
    Ok(())
}

pub fn get_user_message(u: BSUser, offset: u32) -> SQLResult<Vec<DBMessage>> {
    let r_offset = max(u.offset.unwrap_or(0), offset);

    let db = Connection::open(APP_CONFIG.write().unwrap().get_bs_db())?;
    let mut stmt = db.prepare(
        "SELECT id, from_user, to_cap, text, freq FROM message \
        where id > ?1 ORDER BY id ASC LIMIT 1",
    )?;
    let messages: Vec<DBMessage> = stmt
        .query_map([r_offset], |row| {
            Ok(DBMessage {
                id: row.get(0)?,
                from_user: row.get(1)?,
                to_cap: row.get(2)?,
                text: row.get(3)?,
                freq: row.get(4)?,
            })
        })?
        .map(|r| r.unwrap())
        .collect();
    update_bs_offset(u, r_offset + 1)?;
    Ok(messages)
}

pub fn update_bs_offset(u: BSUser, offset: u32) -> SQLResult<()> {
    let db = Connection::open(APP_CONFIG.write().unwrap().get_bs_db())?;
    let mut stmt = db.prepare("UPDATE pager_bs SET offset = ?1 WHERE name = ?2")?;
    stmt.execute([offset.to_string(), u.username])?;
    Ok(())
}
